﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using Model.Ad;
using SqlOperate;
using nspLable.Function;
using System.Web;
using System.Text;
using Base;

namespace DAL.Ad
{
    /// <summary>
    /// MemberInfo 会员提供信息类
    /// </summary>
    public class MemberInfo
    {
        public static string ConnectionString = ConfigurationManager.ConnectionStrings["sqlData"].ConnectionString;
        #region
        public MemberInfo(){}
        /// <summary>
        /// 会员添加广告信息
        /// </summary>
        /// <param name="t_id">广告位ID</param>
        /// <param name="userid">用户ID</param>
        /// <param name="UMName">申请广告人的名称（即会员或者管理员）</param>
        /// <param name="IsUM">标志管理员和会员</param>
        /// <param name="adi"></param>
        public void AddMemberAdInfo(int t_id,int userid,string IsUM,string UMName,AdMemberInfo adm) 
        {
            SqlParameter[] parms ={
            new SqlParameter ("@User_ID",userid),
            new SqlParameter ("@T_ID",t_id),
            new SqlParameter ("@Ad_Adrees",adm.M_Adrees),
            new SqlParameter ("@Ad_Leter",adm.MLeter),
            new SqlParameter ("@Ad_ImageAdrees",adm.MImageAdrees),
            new SqlParameter ("@Ad_Day",adm.MDay),
            new SqlParameter ("@MemberInfoName",adm.Ad_MemberInfo_Name),
            new SqlParameter ("@Ad_StartDate",DateTime.Now.ToShortTimeString()),
            new SqlParameter ("@UM_Name",UMName),
            new SqlParameter("@UM_Sign",IsUM)
            };
      
            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure,"SP_W_Ad_MemberInfo_Insert", parms);
        }
        /// <summary>
        /// 会员更新广告信息
        /// </summary>
        /// <param name="upid">会员更新的数据id</param>
        /// <param name="adm">会员对象</param>
        public void UpdateMemberAdInfo(int upid,AdMemberInfo adm) 
        {
            SqlParameter[] parms ={ 
            new SqlParameter ("@Ad_Adrees",adm.M_Adrees),
            new SqlParameter ("@Ad_Leter",adm.MLeter),
            new SqlParameter ("@Ad_ImageAdrees",adm.MImageAdrees),
            new SqlParameter ("@Ad_Day",adm.MDay),
            new SqlParameter ("@upid",upid),
            new SqlParameter ("@MemberInfoName",adm.Ad_MemberInfo_Name),
            new SqlParameter ("@Ad_StartDate",DateTime.Now.ToShortDateString())
            };

            SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure,"SP_W_Ad_MemberInfo_Update", parms);
        }
        /// <summary>
        /// 根据用户ID查询信息
        /// </summary>
        /// <param name="userid">用户ID</param>
        /// <returns>返回列表</returns>
        public DataTable SelectMemberAdInfo(int userid) 
        {
            SqlParameter[] parms ={ 
            new SqlParameter ("@userid",userid)
            };
            return SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "SP_W_Ad_MemberInfo_Select",parms).Tables[0];
        }
        
        /// <summary>
        /// 审核广告方法
        /// </summary>
        public void ShenHe(int mid,string str)
        {
            string senhe = "通过";
            string update = "update W_Ad_MemberInfo set Ad_Auditing=@senhe where M_ID=@mid";
            SqlParameter[] parms ={ 
            new SqlParameter ("@mid",mid),
            new SqlParameter ("@senhe",str)
            };
            SqlHelper.ExecuteNonQuery(CommandType.Text, update, parms);
        }
        /// <summary>
        /// 返回会员申请的信息信息实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public AdMemberInfo GetModelMemberAdInfo(string strWhere) 
        {
              string str = "select Ad_Adrees,Ad_ImageAdrees,M_ID,Ad_Leter,Ad_Day,Ad_MemberInfoName from W_Ad_MemberInfo "+strWhere;
            AdMemberInfo adm = new AdMemberInfo();
            using (SqlDataReader read = SqlHelper.ExecuteReader(CommandType.Text, str,null)) 
            {
                if (read.Read())
                {
                    adm.M_Adrees = read["Ad_Adrees"].ToString();
                    adm.MImageAdrees = read["Ad_ImageAdrees"].ToString();
                    adm.MDay = Convert.ToInt32(read["Ad_Day"].ToString());
                    adm.Ad_MemberInfo_Name = read["Ad_MemberInfoName"].ToString();
                    if (read["Ad_Leter"].ToString() != "")
                    {
                        adm.MLeter = read["Ad_Leter"].ToString();
                    }
                }
                else
                {
                    adm = null;
                }
            }
            return adm;
        }
        public DataTable GetAD(int num, int adtypeID)
        {
            DataTable dt = SqlHelper.ExecuteSqlQuery("SELECT top " + num + " a.*,b.Ad_Width,b.Ad_Height FROM [W_Ad_MemberInfo] as a, [W_Ad_Table] as b WHERE a.T_ID=b.T_ID and a.T_ID=" + adtypeID);
            return dt;
        }

        public string TypeLink(string strTypeLink, string param)
        {
            if (strTypeLink[strTypeLink.Length - 1] == ',')
            {
                strTypeLink = strTypeLink.Substring(0, strTypeLink.Length - 1);
            }
            try
            {
                DataTable TypeData = new DataTable();
                if (param == "Detail")
                {
                    TypeData = Function.GetDataTable("Type,ID", "where ID in(" + strTypeLink + ") ", "W_IndustryType", " order by SuperiorsFlag  ");
                }
                else
                {
                    SqlParameter[] param1 = new SqlParameter[] 
                    {  
                        new SqlParameter("@ID",Convert.ToInt32(strTypeLink))
                    };
                    TypeData = SqlOperate.SqlHelper.ExecuteTable(CommandType.StoredProcedure, "SP_W_TypeLink", param1);
                }
                string strType = "";
                string str = "<a href=\"trade-";

                if (TypeData.Rows.Count > 0)
                {
                    for (int i = 0; i < TypeData.Rows.Count; i++)
                    {
                        strType += " > " + str;
                        strType += TypeData.Rows[i]["id"].ToString();
                        strType += Base.BasePage.Config.Rewrite + "\">";
                        strType += TypeData.Rows[i]["Type"].ToString();
                        strType += "</a>";
                    }
                }
                return strType;
            }
            catch (Exception ex)
            {
                return "";
            }
        }

        public string lowerType(int strType, string p)
        {
            int TypeID = 0;
            if (HttpContext.Current.Request.QueryString["id"] != null && HttpContext.Current.Request.QueryString["id"] != "")
            {
                if (Base.Common.CheckNum(HttpContext.Current.Request.QueryString["ID"]))
                {
                    TypeID = Convert.ToInt32(HttpContext.Current.Request.QueryString["ID"]);
                }
            }


            StringBuilder ReBuilder = new StringBuilder();
            DataTable TypeDate = Function.GetDataTable(" distinct  a.[Type],a.ID,sequence,(select count(UserID) from U_UserInfo where patindex('%,'+rtrim(a.ID)+',%',','+ Industry+',')>0) as countcomp ", " where ToRelease=1 AND SuperiorsID=" + TypeID + " ", " W_IndustryType as a left join  U_Userinfo as b on patindex('%,'+rtrim(a.ID)+',%',','+ b.Industry+',')>0", " order by sequence asc ");
            if (TypeDate.Rows.Count > 0)
            {
                ReBuilder.Append(@"<p class='ltnr2'>");
                for (int i = 0; i < TypeDate.Rows.Count; i++)
                {
                    ReBuilder.Append("");
                    if (p == "Supply")
                    {
                        ReBuilder.Append(@"<span><a href='{PB_WebURL}Supply/Trade-");
                    }
                    else if (p == "Purchase")
                    {
                        ReBuilder.Append(@"<span><a href='{PB_WebURL}purchase/Trade-");
                    }
                    else if (p == "Product")
                    {
                        ReBuilder.Append(@"<span><a href='{PB_WebURL}Product/Trade-");
                    }
                    else if (p == "Expo")
                    {
                        ReBuilder.Append(@"<span><a href='{PB_WebURL}Expo/Detail-");
                    }
                    else
                    {
                        ReBuilder.Append(@"<span><a href='../Company/Trade-");
                    }
                    ReBuilder.Append(TypeDate.Rows[i]["ID"].ToString() + Base.BasePage.Config.Rewrite);
                    ReBuilder.Append(@"'>");
                    ReBuilder.Append(TypeDate.Rows[i]["Type"].ToString());
                    ReBuilder.Append("("+TypeDate.Rows[i]["countcomp"].ToString()+")");
                    ReBuilder.Append(@"</a></span>");
                }
                ReBuilder.Append(@"</p>");
            }
            else
                ReBuilder.Append(@"<p class='ltnr2'><span>暂无下级分类！</span></p>");
            return ReBuilder.ToString();
        }

        public DataTable GetCompanylist( int PageSize,int PageIndex,string strWhere)
        {
            //string strWhere = " U_UserInfo.Verify=1 AND U_UserInfo.GradeID=U_VipGrade.ID and datediff(hh,U_UserInfo.EndDate,getdate())>0";
         
            string strTable = " U_UserInfo,U_VipGrade ";
            int OrderType = 0;
            string OrderField = " Sort desc ,RegDate ";
            string strSelect = " U_UserInfo.*,U_VipGrade.Flag ";
            //int PageSize = 10;
            //int PageIndex = 1;
            if (HttpContext.Current.Request.QueryString["Page"] != null)
                PageIndex = Convert.ToInt32(HttpContext.Current.Request.QueryString["Page"]);
            if (PageIndex < 1) PageIndex = 1;
            int Count = 0;

            DataTable Lists = Pagination.GetDataTable(strWhere, strTable, OrderType, OrderField, strSelect, PageSize, PageIndex, Count, "U_UserInfo.UserID");
            return Lists;
        }

        public DataTable GetCompanylist(string strWhere,string orderStr)
        {
            string strsql = "SELECT U_UserInfo.*,U_VipGrade.Flag,U_VipGrade.Name as vipName FROM U_UserInfo,U_VipGrade ";
            if (strWhere != "")
            {
                strsql += " where " + strWhere;
            }
            if (orderStr != "")
            {
                strsql += orderStr;
            }
            //DataTable dt = SqlHelper.ExecuteSqlQuery("SELECT U_UserInfo.*,U_VipGrade.Flag FROM U_UserInfo,U_VipGrade WHERE " + strWhere+" " );
            DataTable dt = SqlHelper.ExecuteSqlQuery(strsql);
            return dt;
        }

        public string GetIndustryType(string ids)
        {
            string retVal = "";
            SqlParameter[] param = new SqlParameter[] 
            {
                new SqlParameter("@ids", ids)
            };
            DataTable dt = SqlHelper.ExecuteTable(CommandType.StoredProcedure, "SP_W_selectIndustryType", param);
            if (dt.Rows.Count != 0)
            {
                retVal = dt.Rows[0]["industryType"].ToString();
            }
            return retVal;
        }

        public DataTable GetZsTab(int num, string strWhere)
        {
            string str = "SELECT top " + num + " a.*,b.Name as tName FROM [W_SincereAgent] as a left join W_AgentType as b on a.AgentType=b.ID ";
            if (strWhere != "")
            {
                str += " where " + strWhere;
            }
            str += " order by AddDate desc";
            DataTable dt = SqlHelper.ExecuteSqlQuery(str);
            return dt;
        }


        public DataTable GetHotIndustry( int num)
        { 
              string str = "select ";
            if (num > 0)
            {
                str += " top " + num;
            }
            str += " a.ID, a.Type, count(b.UserID) from  dbo.W_IndustryType as a, dbo.U_UserInfo as b  where patindex('%,'+rtrim( a.ID)+',%',','+ b.Industry+',')>0 and b.Verify=1 group by a.Type,a.ID order by count(b.UserID) desc  ";
            DataTable dt = SqlHelper.ExecuteSqlQuery(str);
            return dt;
        }
        public DataTable GetcompanyCount(string strWhere)
        {
            string str = " select * from U_UserInfo";
            if (strWhere != "")
            {
                str += " where " + strWhere;
            }
            DataTable dt = SqlHelper.ExecuteSqlQuery(str);
            return dt;
        }
        #endregion
    }
       
}